<?php
require_once '../global.inc.php';
Database::connect($HOST, $NAME, $PWD, $DB);
$table 			= "member";
// operation操作类型：add、del、query、edit、exp、imp
$operation 		= $_GET["op"];

//展示用户列表
if ($operation=="query") {
	//获取页面信息
	$memberName 	= $_POST["memberName"];
	$netName 		= $_POST["netName"];
	$memberNo		= $_POST["memberNo"];
	$idNumber		= $_POST["idNumber"];
	$mobile			= $_POST["mobile"];
	$qq				= $_POST["qq"];
	$joinDateBegin	= $_POST["joinDateBegin"];
	$joinDateEnd	= $_POST["joinDateEnd"];
	$flag			= $_POST["flag"];
	if(!isset($_GET["p"])){//首次查询
		$condition = "1=1";
		$tag	   = $_GET["tag"];      //查询后的操作方式（q列表，e列表编辑，j加入活动）
		if(strlen($memberName)!=0){
			$condition .= " and member_name like '%$memberName%'";
		}
		if(strlen($netName)!=0){
			$condition .= " and net_name like '%$netName%'";
		}
		if(strlen($memberNo)!=0){
			$condition .= " and member_no='$memberNo'";
		}
		if(strlen($idNumber)!=0){
			$condition .= " and id_number='$idNumber'";
		}
		if(strlen($mobile)!=0){
			$condition .= " and mobile=$mobile";
		}
		if(strlen($qq)!=0){
			$condition .= " and qq=$qq";
		}
		if(strlen($joinDateBegin)!=0){
			$condition .= " and join_date>=str_to_date('$joinDateBegin','%Y-%m-%d')";
		}
		if(strlen($joinDateEnd)!=0){
			$condition .= " and join_date<=str_to_date('$joinDateEnd','%Y-%m-%d')";
		}
		if(strlen($flag)!=0){
			$condition .= " and flag=$flag";
		}
		$condition .=" order by member_id desc";
		$_SESSION["memberQryWhere"] = $condition;
		$_SESSION["tag"]			= $tag;
	}else{
		$condition = $_SESSION["memberQryWhere"];
		$tag	   = $_SESSION["tag"];
	}
	//echo $condition;

	$count			= Database::count($table,$condition);	
	$totalRows		= $count[0];				//数据总量
	$currentPage	= $_GET["p"];				//当前页面
	$pageInfo		= array();					//初始化页面信息
	$subpages 		= new SubPages($PAGE_SIZE, $totalRows, $currentPage);	//实例化分页
	$pageInfo 		= $subpages->getPageArray();

	
	//查询本页数据
	$memberArray 		= Database::findPage($table, $pageInfo["startRow"], $pageInfo["pageSize"],$condition);

	//将分页信息和本页数据写入session
	$_SESSION["pageInfoMember"]	= $pageInfo;
	$_SESSION["memberArray"]	= $memberArray;
	echo "<script>window.location.href='../view/member/memberList.php';</script>";
}

//检查身份证号是否存在
if ($operation=="check") {
	$idNumber = $_GET["idNumber"];
	$count = Database::count("member","id_number = '$idNumber'");
	//ajax返回数据
	echo $count[0];
}

//客户资料新增
if ($operation=="add") {
	$memberName		= $_POST["memberName"];
	$netName		= $_POST["netName"];
	$sex			= $_POST["sex"];
	$memberNo		= $_POST["memberNo"];
	$idNumber		= $_POST["idNumber"];
	$mobile			= $_POST["mobile"];
	$email			= $_POST["email"];
	$qq				= $_POST["qq"];
	$birthday		= $_POST["birthday"];
	$company		= $_POST["company"];
	$comments		= $_POST["comments"];
	$joinDate		= date("Y-m-d");		//加入时间
	$score			= 0;					//初始积分
	$flag			= 1;					//默认有效
	Database::begin();
	if(strlen($memberNo)){//会员卡号不为空
		$count = Database::count("member","member_no = '$memberNo'");
		if($count[0]>0){
			echo "<script>alert('该会员卡号已被分配给其他会员！');</script>";
			echo "<script>window.location.href='../view/member/memberAdd.php';</script>";
			exit;
		}
	}else{//自动生成会员卡号(自动生成的卡号都是以668开头的10位数字)
		$maxNo = Database::max("member", "member_no","member_no like '668%'");
		if(empty($maxNo[0])){
			$memberNo = '6680000000';
		}else{
			$memberNo = $maxNo[0]+1;
		}
	}
	//插入新客户资料
	$sql = "INSERT INTO member(member_name,net_name,sex,id_number,birthday,mobile,email,qq,company,member_no,join_date,score,flag,comments)"
		  ."VALUES ('$memberName','$netName','$sex','$idNumber','$birthday',$mobile,'$email','$qq','$company','$memberNo','$joinDate','$score',$flag,'$comments')";
	Database::insert($sql);

	Database::commit();
	Database::end();
	//转向展示页面
	echo "<script>alert('会员增加成功！');</script>";
	echo "<script>window.location.href='memberAction.php?op=query';</script>";
}
//失效或者恢复某一个用户的资料
if($operation=="valid"||$operation=="invalid"){
	$id				= $_GET["id"];
	if($operation=="valid"){//恢复
		$sql	= "update member set flag=1 where member_id in ($id)";
	}else if($operation=="invalid"){//失效
		$sql	= "update member set flag=0 where member_id in ($id)";
	}
	Database::update($sql);
	//转向展示页面
	echo "<script>window.location.href='memberAction.php?op=query&tag=e';</script>";
}

//获取某一个会员的资料
if ($operation=="get") {
	$id				= $_GET["id"];
	$tag			= $_GET["tag"];
	$idName			= "member_id";

	//查询会员信息
	$memberInfo		= Database::findById($table, $idName, $id);

	//将取得的会员信息写入session
	$_SESSION["memberInfo"]	= $memberInfo;

	//转向处理页面
	if($tag=="e"){
		echo "<script>window.location.href='../view/member/memberEdit.php';</script>";
	}else{
		echo "<script>window.location.href='../view/member/memberDetail.php';</script>";
	}
}

//修改会员的资料
if ($operation=="edit") {
	$id				= $_GET["id"];
	$memberName		= $_POST["memberName"];
	$netName		= $_POST["netName"];
	$sex			= $_POST["sex"];
	$memberNo		= $_POST["memberNo"];
	$idNumber		= $_POST["idNumber"];
	$mobile			= $_POST["mobile"];
	$email			= $_POST["email"];
	$qq				= $_POST["qq"];
	$birthday		= $_POST["birthday"];
	$company		= $_POST["company"];
	$comments		= $_POST["comments"];

	$count = Database::count("member","member_no = '$memberNo' and member_id <> $id");
	if($count[0]>0){
		echo "<script>alert('该会员卡号已被分配给其他会员！');</script>";
		echo "<script>window.location.href='../view/member/memberQuery.php?tag=e';</script>";
		exit;
	}
	
	$sql 			= "update member set 
						member_name = '$memberName',
						net_name 	= '$netName',
						sex 		= '$sex',
						member_no 	= '$memberNo',
						id_number 	= '$idNumber',
						mobile	 	= '$mobile',
						email		= '$email',
						qq   		= '$qq',
						birthday 	= '$birthday',
						company 	= '$company',
						comments 	= '$comments'";
	$sql .= " where member_id = $id";

	Database::update($sql);

	//转向展示页面
	echo "<script>alert('会员资料修改成功！');</script>";
	echo "<script>window.location.href='memberAction.php?op=query&tag=e';</script>";
}

//导出数据
if ($operation=="exp") {
	$sql = "select * from member order by member_id asc";
	$memberArray = Database::find($sql);
	$str = "姓名,网名,性别,身份证号,生日,手机号码,电子邮件,QQ,公司,会员卡号,加入时间,积分,备注\n";
	$str = iconv('utf-8','gb2312',$str);
	foreach ($memberArray as $memberInfo){
		$memberName = iconv('utf-8','gb2312',$memberInfo['member_name']); //中文转码
		if ($memberInfo['sex']==1) {$sex = '男';}else {$sex = '女';}
		$sex = iconv('utf-8','gb2312',$sex);
		$netName = iconv('utf-8','gb2312',$memberInfo['net_name']); //中文转码
		$idNumber = $memberInfo['id_number'];
		$birthday = $memberInfo['birthday'];
		$mobile	  = $memberInfo['mobile'];
		$email    = $memberInfo['email'];
		$qq		  = $memberInfo['qq'];
		$company  = iconv('utf-8','gb2312',$memberInfo['company']); //中文转码
		$memberNo = $memberInfo['member_no'];
		$joinDate = $memberInfo['join_date'];
		$score	  = $memberInfo['score'];
		$comments  = iconv('utf-8','gb2312',$memberInfo['comments']); //中文转码
		$str .= $memberName.",".$netName.",\t".$sex.",\t".$idNumber.",\t".$birthday.",\t".$mobile.",\t".$email.",\t".$qq.","
			 .$company.",\t".$memberNo.",\t".$joinDate.",\t".$score.",\t".$comments."\n"; //用引文逗号分开
	}
	$filename = 'member_'.date('Ymd').'.csv'; //设置文件名
	export_csv($filename,$str); //导出
}

function export_csv($filename,$data) {
	header("Content-type:text/csv");
	header("Content-Disposition:attachment;filename=".$filename);
	header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
	header('Expires:0');
	header('Pragma:public');
	echo $data;
}

//导入数据
if ($operation=="imp") {
	setlocale(LC_ALL, 'zh_CN');//设置地区编码，否则fgetcsv会出现中文无法处理的情况
	$filename = $_FILES ['file'] ['tmp_name'];
	if (empty ( $filename )) {
		echo "<script>alert('请选择要导入的CSV文件！');window.location.href='../view/member/memberImpExp.php'</script>";
		exit ();
	}
	$handle = fopen ( $filename, 'r' );
	$result = input_csv ( $handle ); // 解析csv
	$len_result = count ( $result );
	if ($len_result == 0) {
		echo "<script>alert('没有任何数据！');window.location.href='../view/member/memberImpExp.php'</script>";
		exit ();
	}
	//print_r($result);
	for($i = 1; $i < $len_result; $i ++) { // 循环获取各字段值
		$memberName 	= $result[$i][0];
		$netName 		= $result[$i][1];
		$sex			= $result[$i][2];
		$idNumber		= $result[$i][3];
		$mobile			= $result[$i][4];
		$email			= $result[$i][5];
		$qq				= $result[$i][6];
		$company		= $result[$i][7];
		$memberNo		= $result[$i][8];
		$score			= $result[$i][9];
		$comments		= $result[$i][10];
		
		//数据格式检查、中文转码
		//会员名：（必填）
		if(!strlen($memberName)){
			$str = "第".$i."行的会员名为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		$memberName = iconv('gb2312','utf-8',$memberName);
		
		//网名(有值才检查）
		if(strlen($netName)){
			$netName = iconv('gb2312','utf-8',$netName);
		}
		
		//性别:（必填）
		$sex = iconv('gb2312','utf-8',$sex);
		if(!strlen($sex)){
			$str = "第".$i."行的性别为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}else if($sex!='男'&&$sex!='女'){
			$str = "第".$i."行的性别输入值非法，只能是男或者女，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		if ($sex=='男') {$sex = 1;}else {$sex = 0;}
		
		//身份证检查（必填）
		if(!strlen($idNumber)){
			$str = "第".$i."行的身份证为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}else if(strlen($idNumber)!=15&& strlen($idNumber)!=18){
			$str = "第".$i."行的身份证位数不正确，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		$count = Database::count("member","id_number = '$idNumber'");
		if ($count[0]>0) {
			$str = "第".$i."行的会员的身份证在系统已经存在，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		
		//生日计算
		if(strlen($idNumber)==18){
			$birthday = substr($idNumber,6,4)."-".substr($idNumber,10,2)."-".substr($idNumber,12,2);
		}else if(strlen($idNumber)==15){
			$birthday = "19".substr($idNumber,6,2)."-".substr($idNumber,8,2)."-".substr($idNumber,10,2);
		}
		
		//手机号码检查（必填）
		if(!strlen($mobile)){
			$str = "第".$i."行的手机号码为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}else if(strlen($mobile)!=11||substr($mobile,0,1)!=1){
			$str = "第".$i."行的手机号码格式不正确，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		
		//电子邮件(有值才检查）
		if(strlen($email)){
			if(!stristr($email,'@')){
				$str = "第".$i."行的电子邮件格式不正确，不允许导入！";
				echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
				exit;
			}
		}
		
		//QQ(有值才检查）
		if(strlen($qq)){
			if(!is_numeric($qq)){
				$str = "第".$i."行的QQ号码不是数字，不允许导入！";
				echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
				exit;
			}
		}
		
		//公司名(有值才检查）
		if(strlen($company)){
			$company = iconv('gb2312','utf-8',$company);
		}
		
		//会员卡号（必填）
		if(!strlen($memberNo)){
			$str = "第".$i."行的会员卡号为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		$count = Database::count("member","member_no = '$memberNo'");
		if ($count[0]>0) {
			$str = "第".$i."行的会员的卡号在系统已经存在，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		
		//会员积分(必填)
		if(!strlen($score)){
			$str = "第".$i."行的会员积分为空，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}else if(!is_numeric($score)||$score<0){
			$str = "第".$i."行的会员积分不是大于0的数字，不允许导入！";
			echo "<script>alert('$str');window.location.href='../view/member/memberImpExp.php'</script>";
			exit;
		}
		
		//备注(有值才检查）
		if(strlen($comments)){
			$comments = iconv('gb2312','utf-8',$comments);
		}
		//加入时间
		$joinDate		= date("Y-m-d");		
		//状态
		$flag			= 1;					//默认有效
		
		$data_values .= "('$memberName','$netName','$sex','$idNumber','$birthday','$mobile','$email','$qq','$company','$memberNo','$joinDate','$score','$flag','$comments'),";
	}
	$data_values = substr($data_values,0,-1 ); // 去掉最后一个逗号
	fclose ( $handle ); // 关闭指针
	$sql = "INSERT INTO member(member_name,net_name,sex,id_number,birthday,mobile,email,qq,company,member_no,join_date,score,flag,comments) values $data_values"; // 批量插入数据表中
	$result = Database::insert($sql);
	if ($result) {
		echo "<script>alert('导入会员资料成功！');window.location.href='../view/member/memberImpExp.php';</script>";
	} else {
		echo "<script>alert('导入会员资料失败！');window.location.href='../view/member/memberImpExp.php'</script>";
	}
}
function input_csv($handle) {
	$out = array();
	$n = 0;
	while ($data = fgetcsv($handle, 10000)) {
		$num = count ( $data );
		for($i = 0; $i < $num; $i ++) {
			$out [$n] [$i] = $data [$i];
		}
		$n ++;
	}
	return $out;
		
}
//关闭数据库
Database::close();
?>